# 3  对2中的表结构，用SQLAchemy模块来实现同样的操作；

import datetime
import time
from sqlalchemy import create_engine, String, Column, INTEGER
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()


class Message(Base):
    __tablename__ = 'message'

    id = Column(INTEGER, primary_key=True)
    message = Column(String)
    message_name = Column(String)
    message_time = Column(String)
    delect_YN = Column(String)


    def __init__(self, message, name):
        self.message_time =  datetime.datetime.now()
        self.id = int(time.time())
        self.delect_YN = 'N'
        self.message_name = name
        self.message = message
db_config = {
    'host': 'localhost',
    'port': '3306',
    'database': 'Message',
    'username': 'xulili',
    'password': '456281937'
}
# 数据库链接地址
db_url = 'mysql+pymysql://{username}:{password}@{host}:{port}/{database}?charset=utf8'.format(**db_config)
# 创建数据库引擎
engine = create_engine(db_url)


def add(message, message_name):
    engine = create_engine(db_url)
    try:
         DbSession = sessionmaker(bind=engine)
         session = DbSession()
         add_user = Message(message, message_name)
         session.add(add_user)
         session.commit()
    except:
        print("Error:add failed")
    session.close()

def delete(id):
    engine = create_engine(db_url)
    try:
        DbSession = sessionmaker(bind=engine)
        session = DbSession()
        delete_users = session.query(Message).filter(Message.id == id).first()
        if delete_users:
            session.delete(delete_users)
            session.commit()
    except:
        print("Error:delete failed")
    session.close()

def find(name):
        engine = create_engine(db_url)
    #try:
        DbSession = sessionmaker(bind=engine)
        session = DbSession()
        results = session.query(Message).filter(Message.message_name == name).all()
        for items in results:
            print("%d %s %s %s"%(items.id,items.message,items.message_name,items.message_time))
    #except:
        #print("Error:query failed")
        session.close()

def update(new_message, ID):
    engine = create_engine(db_url)
    try:
        DbSession = sessionmaker(bind=engine)
        session = DbSession()
        me = session.query(Message).filter(Message.id == ID).first()
        me.message = new_message
        session.commit()
    except:
        print("Error:update failed")


if __name__ == '__main__':
    while True:
        print('1. 增加信息：')
        print('2. 删除信息')
        print('3. 更新数据')
        print('4. 查询信息' )
        print('0.退出')
        index = input('请输入操作菜单选项:')
        if index == '0':
            break
        elif index == '1':
            x = input("请输入留言内容并以及留言人姓名（用/隔开）：")
            xlist = x.split('/')
            message = xlist[0]
            name = xlist[1]
            add(message, name)
        elif index == '2':
            x = int(input("请输入要删除内容的ID："))
            delete(x)
        elif index == '3':
            x = input("请输入想要更改内容 以及更改对象的id（用/隔开)：")
            xlist = x.split('/')
            message = xlist[0]
            id = int(xlist[1])
            print(id)
            update(message, id)
        elif index == '4':
            x = input("请输入要查询的留言人：")
            find(x)